﻿using SupermarketSalesModel;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SupermarketSalesController
{
    class SQLServerDAOCreditNote : DAOCreditNote
    {
        public void add(CreditNote p)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO G3_CreditNote(amount, registerDate,status,idSale) "+
                                    "VALUES (@param1,@param2,@param3,@param4)";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Int);
                

                param1.Value = p.Amount;
                param2.Value = p.RegisterDate;
                param3.Value = p.Flag;
                param4.Value = p.Sale.Id;
                
                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                
                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();

                //TAMBIEN SE DEBEN AGREGAR SUS DETALLES A LA BASE DE DATOS
                foreach (CNDetail cnD in p.Details)
                {
                    SalesManager.addCNDetail(cnD);
                }

            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void update(CreditNote p)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;

                comm.CommandText = "UPDATE G3_CreditNote SET amount=@param1, registerDate=@param2," +
                        "status=@param3, idSale=@param4 WHERE idCN=@param5";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Float);
                SqlParameter param2 = new SqlParameter("@param2",
                                                        System.Data.SqlDbType.DateTime);
                SqlParameter param3 = new SqlParameter("@param3",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param4 = new SqlParameter("@param4",
                                                        System.Data.SqlDbType.Int);
                SqlParameter param5 = new SqlParameter("@param5",
                                                        System.Data.SqlDbType.Int);

                param1.Value = p.Amount;
                param2.Value = p.RegisterDate;
                param3.Value = p.Flag;
                param4.Value = p.Sale.Id;
                param5.Value = p.Id;

                comm.Parameters.Add(param1);
                comm.Parameters.Add(param2);
                comm.Parameters.Add(param3);
                comm.Parameters.Add(param4);
                comm.Parameters.Add(param5);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public void delete(int CreditNoteId)
        {
            SqlConnection conn = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;


                //PASO EXTRA: ANTES DE ELIMINAR ESTA NOTA DE CREDITO SE DEBEN ELIMINAR SUS DETALLES DE LA BASE DE DATOS
                List<CNDetail> detailsCN = SalesManager.getAllCreditNoteDetails (CreditNoteId); //CORRIGE ACA MARSHALL
                foreach (CNDetail cnD in detailsCN)
                {
                    SalesManager.deleteCNDetail(cnD.Id);
                }
                //////////////////////////////////////////////////////////////////////////////////////////////////////

                comm.CommandText = "DELETE FROM G3_CreditNote " +
                                            " WHERE idCN=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = CreditNoteId;

                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                comm.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
        }

        public List<CreditNote> queryAll()
        {
            List<CreditNote> list = new List<CreditNote>();
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_CreditNote";

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                while (myReader.Read())
                {
                    CreditNote p = new CreditNote();
                   
                    p.Id = Int32.Parse(myReader["idCN"].ToString());
                    p.Flag = Int32.Parse(myReader["status"].ToString());
                    p.Amount = Double.Parse(myReader["Amount"].ToString());
                    p.RegisterDate = DateTime.Parse(myReader["RegisterDate"].ToString());

                    int idSale = Int32.Parse(myReader["IdSale"].ToString());
                    p.Sale = SalesManager.getSaleById(idSale);

                    p.Details = SalesManager.getAllCreditNoteDetails( p.Id ); //CORRIGE ACA MARSHALL
                    list.Add(p);
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 6. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return list;
        }

        public CreditNote queryById(int CreditNoteId)
        {
            CreditNote p = null;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM G3_CreditNote WHERE idCN=@param1";
                SqlParameter param1 = new SqlParameter("@param1",
                                                        System.Data.SqlDbType.Int);
                param1.Value = CreditNoteId;
                comm.Parameters.Add(param1);

                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    p = new CreditNote();
                    p.Id = Int32.Parse(myReader["idCN"].ToString());
                    p.Flag = Int32.Parse(myReader["status"].ToString());
                    p.Amount = Double.Parse(myReader["Amount"].ToString());
                    p.RegisterDate = DateTime.Parse(myReader["RegisterDate"].ToString());
                    
                    int idSale = Int32.Parse(myReader["IdSale"].ToString());
                    p.Sale = SalesManager.getSaleById(idSale);

                    p.Details = SalesManager.getAllCreditNoteDetails( p.Id ); //CORRIGE ACA MARSHALL
                    
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return p;
        }


        public int count()
        {
            int quantity = 0;
            SqlConnection conn = null;
            SqlDataReader myReader = null;
            try
            {
                //Paso 1 y 2: Registrar el driver y obtener la conexión
                conn = new SqlConnection();
                conn.ConnectionString = "Server=inti.lab.inf.pucp.edu.pe;" +
                    "Database=inf282;User ID=inf282;Password=inf282db;";
                conn.Open();

                //Paso 3. Preparar la sentencia
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT Count(1) AS quantity FROM G3_CreditNote";
                //Paso 4. Ejecutar la sentencia
                myReader = comm.ExecuteReader();

                //Paso 5: Evaluar los resultados
                if (myReader.Read())
                {
                    quantity = Int32.Parse(myReader["quantity"].ToString());
                }
            }
            catch (Exception e)
            {
                // TODO Auto-generated catch block			
                Console.WriteLine(e.StackTrace); //Se debe manejar con logs
                throw e;
            }
            finally
            {
                //Paso 5. CERRAR LA CONEXIÓN!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
                try { if (myReader != null) myReader.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
                try { if (conn != null) conn.Close(); }
                catch (Exception e) { Console.WriteLine(e.StackTrace); }
            }
            return quantity;
        }

    
    }
}
